package br.edu.udc.sistemas.ia5.query;

import br.edu.udc.sistemas.ia5.entity.Entity;

public class Query {

	public static String getSQLInsert(Entity obj){
		String sql = "insert into " + obj.getTableName()
					+ " (" + getFieldNames(obj, false, false) + ") " 
					+ "values (" + getFieldValues(obj) + ")"; 
		return sql;
	}
	
	public static String getSQLUpdate(Entity obj){
		String sql = "update " + obj.getTableName()
					+ " set " + getFieldNamesValues(obj)
					+ " where " + obj.getIdName() + " = "+obj.getIdValue();
		return sql;
	}
	
	public static String getSQLDelete(Entity obj){
		String sql = "delete from " + obj.getTableName()
					+ " where " + obj.getIdName() + " = " + obj.getIdValue();
		return sql;
	}
	
	public static String getSQLSelect(Entity obj){
		String sql = "select " + getFieldNames(obj, true, true)
					+ " from " + obj.getTableName() + " "
					+ getSQLWhere(obj) + " order by " + obj.getIdName();
		return sql;
	}
	
	private static String getFieldNames(Entity obj, Boolean bId, Boolean bUpper){
		String fieldNames[] = obj.getFieldNames();
		String retorno = "";
		Boolean bFirst = true;
		for (int i = 0; i < fieldNames.length; i++) {
			if ((bId) ||
				(!fieldNames[i].equals(obj.getIdName()))){
				if (bFirst){
					retorno = fieldNames[i];
					bFirst = false;
				} else {
					retorno += ", "+fieldNames[i];
				}
			}
		}
		return retorno;
	}
	
	private static String getFieldValues(Entity obj){
		String fieldNames[] = obj.getFieldNames();
		String fieldValues[] = obj.getFieldValues();
		String retorno = "";
		String bQuote;
		Boolean bFirst = true;
		for (int i = 0; i < fieldNames.length; i++) {
			if (!fieldNames[i].equals(obj.getIdName())){
				if (fieldValues[i] == null){
					bQuote = "";
				} else {
					bQuote = "'";
				}
				if (bFirst){
					if (!(fieldNames[i].startsWith("id"))){ 
						retorno = "upper("+bQuote + fieldValues[i]+ bQuote+")";
					} else {
						retorno = bQuote + fieldValues[i]+ bQuote;
					}
					bFirst = false;
				} else {
					if (!(fieldNames[i].startsWith("id"))){ 
						retorno += ", upper("+ bQuote + fieldValues[i]+ bQuote+")";
					} else {
						retorno += ", "+ bQuote + fieldValues[i]+ bQuote;
					}
				}
			}				
		}
		return retorno;	
	}
	
	private static String getFieldNamesValues(Entity obj){
		String fieldNames[] = obj.getFieldNames();
		String fieldValues[] = obj.getFieldValues();
		String retorno = "";
		String bQuote;
		Boolean bFirst = true;
		for (int i = 0; i < fieldNames.length; i++) {
			if (!fieldNames[i].equals(obj.getIdName())){
				if (fieldValues[i] == null){
					bQuote = "";
				} else {
					bQuote = "'";
				}
				if (bFirst){
					if ((!fieldNames[i].startsWith("id"))){
						retorno = fieldNames[i] + " = upper(" + bQuote + fieldValues[i]+ bQuote+")";
					} else {
						retorno = fieldNames[i] + " = " + bQuote + fieldValues[i]+ bQuote;
					}
					bFirst = false;
				} else {
					if ((!fieldNames[i].startsWith("id"))){
						retorno += ", "+ fieldNames[i] + " = upper(" + bQuote + fieldValues[i]+ bQuote+")";
					} else {
						retorno += ", "+ fieldNames[i] + " = " + bQuote + fieldValues[i]+ bQuote;
					}
				}
			}				
		}
		return retorno;	
	}
	
	private static String getSQLWhere(Entity obj){
		String fieldNames[] = obj.getFieldNames();
		String fieldValues[] = obj.getFieldValues();
//		Object dataTypes[] = obj.getDataType(); 
		String retorno = "";
		Boolean bFirst = true;
		for (int i = 0; i < fieldNames.length; i++) {
			if ((fieldValues[i] != null) && 
				(!fieldValues[i].equals("")) &&
				(!fieldValues[i].equals("null"))) {
				
				if (fieldNames[i].equals(obj.getIdName()) || (fieldNames[i].startsWith("id"))){
//				if ((fieldNames[i].equals(obj.getIdName()) || ((dataTypes[i] != null) && (dataTypes[i] instanceof Integer)))){
					if (bFirst){
						retorno = "where " + fieldNames[i] + " = " + fieldValues[i];
						bFirst = false;
					} else {
						retorno += " and "+ fieldNames[i] + " = " + fieldValues[i];
					}
				} else {
					if (bFirst){
						retorno = "where " + fieldNames[i] + " like upper('%" + fieldValues[i] + "%')";
						bFirst = false;
					} else {
						retorno += " and "+ fieldNames[i] + " like upper('%" + fieldValues[i] + "%')";
					}
					
				}
			}
							
		}
		return retorno;	
	}
}
